﻿USE [GestionePolveri]
GO
/****** Object:  StoredProcedure [GP].[ReportDirezionale2]    Script Date: 05/19/2013 20:51:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Giuseppe Dattilo
-- Create date: 21/08/2012
-- Description:	Carica i dati per il report direzionale ordinati per 'Famiglia', 'ColoriAbbinati'...
-- =============================================
CREATE PROCEDURE [GP].[ReportDirezionale2] 
	@DatamovimentoFrom          DateTime     = NULL,
	@DatamovimentoTo            DateTime     = NULL,
	@CodicePolvere              Varchar(100) = NULL,
	@Famiglia                   Varchar(100) = NULL,
	@Impianto                   Varchar(100) = NULL,
	@Applicazione               Char(10)     = NULL,
	@ColoriAbbinati             Varchar(200) = NULL,
	@Fornitore                  Varchar(50) = NULL,
	@CodLotto                   Varchar(25) = NULL,
	@SortOrder                  tinyint = NULL,
	@Tipo                       tinyint = NULL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	WITH cteColoriAbbinati(IDPolvere, CodicePolvere, CodiceColore) AS 
	(
		SELECT DISTINCT A.idpolvere, A.CodicePolvere, C.CodiceColore
		FROM GP.AnagPolveri as A
		INNER JOIN GP.ComposizioneColori as CC ON A.IDPolvere = CC.IDPolvere 
		INNER JOIN GP.AnagColori AS C ON CC.IDColore = C.IDColore
		--INNER JOIN GP.AnagFamiglie AS F ON C.IDFamiglia = F.IDFamiglia
	),
	cteFamiglia(IDPolvere, descrizione, IDFamiglia) AS 
	(
		SELECT DISTINCT A.IDPolvere, F.descrizione, F.IDFamiglia
		FROM GP.AnagPolveri as A
		INNER JOIN GP.ComposizioneColori as CC ON A.IDPolvere = CC.IDPolvere 
		INNER JOIN GP.AnagColori AS C ON CC.IDColore = C.IDColore
		INNER JOIN GP.AnagFamiglie AS F ON C.IDFamiglia = F.IDFamiglia
	),
	cteApplicazioni(idPolvere, Applicazione) AS 
	(
		SELECT DISTINCT A.idPolvere, CC.Applicazione
		FROM GP.AnagPolveri as A
		INNER JOIN GP.ComposizioneColori as CC ON A.IDPolvere = CC.IDPolvere 
	)
	SELECT
	Stuff(( select ',' + Sub.Applicazione AS [text()] from cteApplicazioni as Sub where Sub.idPolvere = cteApplicazioni.idPolvere for xml path('')), 1, 1, '') as 'Applicazione'
	,P1.CodicePolvere
	,Stuff(( select ',' + Sub.Descrizione AS [text()] from cteFamiglia as Sub where Sub.idPolvere = cteFamiglia.idPolvere for xml path('')), 1, 1, '') as 'Famiglia'
	,P1.IDPolvere, P1.Descrizione, P1.Note
	,GP.AnagFornitori.Descrizione AS Fornitore
	,Stuff(( select ',' + Sub.CodiceColore AS [text()] from cteColoriAbbinati as Sub where Sub.codicePolvere = cteColoriAbbinati.codicePolvere order by Sub.CodiceColore for xml path('')), 1, 1, '') as 'ColoriAbbinati'
	,(isnull(CaricoJ, 0.00) - isnull(ScaricoJ, 0.00)) as 'Giacenza'
	,(isnull(ValoreCaricoJ, 0.00) - isnull(ValoreScaricoJ, 0.00)) as 'Valore'
    ,isnull(CaricoJ, 0.00) as Carico
    ,isnull(ScaricoJ, 0.00) as Scarico

    ,isnull(TPU1.AAA, 0.00) as 'Giacenza Att.'
	,TPU.PrezzoUnitario
	
	,P1.ScortaMinima
    ,isnull(SC.[RAL1], 0.00) as Cons_RAL1
    ,isnull(ST.[RAL1], 0.00) as NP_RAL1  
    ,isnull(SCUP.[RAL1], 0.00) as VAL_RAL1   
    
    ,isnull(SC.[RAL2], 0.00) as Cons_RAL2
    ,isnull(ST.[RAL2], 0.00) as NP_RAL2   
    ,isnull(SCUP.[RAL2], 0.00) as VAL_RAL2   
    
    ,isnull(SC.[Verticale], 0.00) as Cons_Verticale
    ,isnull(ST.[Verticale], 0.00) as NP_Verticale
    ,isnull(SCUP.[Verticale], 0.00) as VAL_Verticale
    
    ,isnull(SC.[Sublimato], 0.00) as Cons_Sublimato
    ,isnull(ST.[Sublimato], 0.00) as NP_Sublimato   
    ,isnull(SCUP.[Sublimato], 0.00) as VAL_Sublimato   
    
    ,isnull(SC.[Colorwood Grande], 0.00) as Cons_ColorwoodGrande
    ,isnull(ST.[Colorwood Grande], 0.00) as NP_ColorwoodGrande   
    ,isnull(SCUP.[Colorwood Grande], 0.00) as VAL_ColorwoodGrande   
    
    ,isnull(SC.[Ferro], 0.00) as Cons_Ferro
    ,isnull(ST.[Ferro], 0.00) as NP_Ferro   
    ,isnull(SCUP.[Ferro], 0.00) as VAL_Ferro   
    
    ,isnull(SC.[Allineamento inventario], 0.00) as Cons_AllineamentoInventario
    ,isnull(ST.[Allineamento inventario], 0.00) as NP_AllineamentoInventario   
    ,isnull(SCUP.[Allineamento inventario], 0.00) as VAL_AllineamentoInventario   
    
    ,isnull(SC.[Reso], 0.00) as Cons_Reso
    ,isnull(ST.[Reso], 0.00) as NP_Reso   
    ,isnull(SCUP.[Reso], 0.00) as VAL_Reso   
    
    ,isnull(SC.[Venduto], 0.00) as Cons_Venduto
    ,isnull(ST.[Venduto], 0.00) as NP_Venduto   
    ,isnull(SCUP.[Venduto], 0.00) as VAL_Venduto   
    
    ,isnull(SC.[Smaltito], 0.00) as Cons_Smaltito
    ,isnull(ST.[Smaltito], 0.00) as NP_Smaltito   
    ,isnull(SCUP.[Smaltito], 0.00) as VAL_Smaltito   
    
    ,isnull(SC.[Colorwood Piccolo], 0.00) as Cons_ColorwoodPiccolo
    ,isnull(ST.[Colorwood Piccolo], 0.00) as NP_ColorwoodPiccolo   
    ,isnull(SCUP.[Colorwood Piccolo], 0.00) as VAL_ColorwoodPiccolo   
    
    ,isnull(SC.[Inventario], 0.00) as Cons_Correzione
    ,isnull(ST.[Inventario], 0.00) as NP_Correzione
    ,isnull(SCUP.[Inventario], 0.00) as VAL_Correzione
	
	
	FROM GP.AnagPolveri as P1 with (nolock)
	INNER JOIN GP.AnagFornitori ON P1.IDFornitore = GP.AnagFornitori.IDFornitore
	inner join GP.vw_FamiglieColoriXPolveri on P1.IDPolvere = GP.vw_FamiglieColoriXPolveri.IDPolvere
	left outer join cteFamiglia on cteFamiglia.idpolvere = p1.idpolvere
	left outer join cteColoriabbinati on cteColoriAbbinati.idpolvere = p1.idpolvere
	left outer join cteApplicazioni on cteApplicazioni.idpolvere = p1.idpolvere
	left outer join (
		select gp.movimenti.idpolvere, sum(Quantita) as CaricoJ, sum(gp.lotti.valore) as ValoreCaricoJ
		from gp.movimenti
		inner join gp.causalimagazzino on gp.movimenti.idcausale = gp.causalimagazzino.idcausale 
		INNER JOIN GP.Lotti ON GP.Lotti.idlotto = GP.movimenti.idlotto	
		LEFT OUTER JOIN GP.CausaliScarico ON GP.Movimenti.IDCausaleScarico = GP.CausaliScarico.IDCausaleScarico
		where gp.causalimagazzino.codcausale = 'C' 
		and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (GP.Movimenti.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
		and ((@CodLotto is null) OR (GP.Lotti.CodLotto Like @CodLotto))
		--and (@Impianto is null)
		and ((@Impianto is null) OR (GP.CausaliScarico.CodCausaleScarico = 'I'))		 
		group by gp.movimenti.idpolvere
	) as TC on P1.idpolvere = TC.idpolvere
	left outer join (
		select gp.movimenti.idpolvere, sum(Quantita) as ScaricoJ, sum(gp.lotti.valore) as ValoreScaricoJ
		from gp.movimenti
		inner join gp.causalimagazzino on gp.movimenti.idcausale = gp.causalimagazzino.idcausale 
		INNER JOIN GP.Impianti ON GP.Movimenti.IDImpianto = GP.Impianti.IDImpianto
		INNER JOIN GP.Lotti ON GP.Lotti.idlotto = GP.movimenti.idlotto
		where gp.causalimagazzino.codcausale = 'S' 
		and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (GP.Movimenti.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
		and ((@Impianto is null) OR (GP.Impianti.IDImpianto IN (select param from GP.fn_Parametri(@Impianto, ','))))
		and ((@CodLotto is null) OR (GP.Lotti.CodLotto Like @CodLotto))
		group by gp.movimenti.idpolvere
	) as TS on P1.idpolvere = TS.idpolvere
	
	outer apply (
        select * from
        (
            SELECT
            GP.Movimenti.idpolvere, GP.Impianti.Impianto,sum(GP.Movimenti.Quantita * tpu.PrezzoUnitario) as Valorizzazione
            FROM GP.Movimenti
            INNER JOIN GP.Impianti ON GP.Movimenti.IDImpianto = GP.Impianti.IDImpianto
            INNER JOIN GP.causalimagazzino ON GP.Movimenti.IDcausale = GP.causalimagazzino.IDcausale   
            INNER JOIN GP.Lotti ON GP.Lotti.idlotto = GP.movimenti.idlotto   
            outer apply (
            select top 1 GPLT.prezzounitario
                from gp.lotti as GPLT
                where gplt.idpolvere = GP.Movimenti.IDPolvere
                order by GPLT.dataddt desc
            ) as TPU    
            where GP.causalimagazzino.codcausale = 'S'
                  and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (GP.Movimenti.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
                  and ((@Impianto is null) OR (GP.Impianti.IDImpianto IN (select param from GP.fn_Parametri(@Impianto, ','))))
                  and Movimenti.IDPolvere=P1.IDPolvere
            group by GP.Movimenti.IDPolvere, GP.Impianti.Impianto
        ) DataTable
        PIVOT
        (
           sum(valorizzazione)
           FOR impianto IN ([RAL1],[RAL2],[Verticale],[Sublimato],[Colorwood Grande],
                                    [Ferro],[Allineamento inventario],[Reso],[Venduto],[Smaltito],[Colorwood Piccolo], [Inventario])
        ) PivotTable
        
    ) as SCUP --
	
	outer apply (
	select top 1 GPLT.prezzounitario
		from gp.lotti as GPLT
		where gplt.idpolvere = p1.idpolvere
		order by GPLT.dataddt desc
	) as TPU 	
	outer apply (
		select sum(GPLT.Giacenza) as AAA 
		from gp.lotti as GPLT
		where gplt.idpolvere = p1.idpolvere
	) as TPU1 
	left outer join (
		select * from 
		(
			SELECT
			GP.Movimenti.idpolvere, GP.Movimenti.Quantita, GP.Impianti.Impianto
			FROM GP.Movimenti 
			INNER JOIN GP.Impianti ON GP.Movimenti.IDImpianto = GP.Impianti.IDImpianto 
			INNER JOIN GP.causalimagazzino ON GP.Movimenti.IDcausale = GP.causalimagazzino.IDcausale 	
		    INNER JOIN GP.Lotti ON GP.Lotti.idlotto = GP.movimenti.idlotto		
			where GP.causalimagazzino.codcausale = 'S' 
		          and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (GP.Movimenti.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
		          and ((@Impianto is null) OR (GP.Impianti.IDImpianto IN (select param from GP.fn_Parametri(@Impianto, ','))))
		          and ((@CodLotto is null) OR (GP.Lotti.CodLotto Like @CodLotto))
		) DataTable
		PIVOT
		(
		   sum(quantita)
		   FOR impianto IN ([RAL1],[RAL2],[Verticale],[Sublimato],[Colorwood Grande],
									[Ferro],[Allineamento inventario],[Reso],[Venduto],[Smaltito],[Colorwood Piccolo], [Inventario])
		) PivotTable
	) as SC on P1.idpolvere = SC.idpolvere
	left outer join (
		select * from 
		(
			SELECT
			GP.Movimenti.idpolvere, GP.Movimenti.Quantita, GP.Impianti.Impianto
			FROM GP.Movimenti 
			INNER JOIN GP.Impianti ON GP.Movimenti.IDImpianto = GP.Impianti.IDImpianto 
			INNER JOIN GP.causalimagazzino ON GP.Movimenti.IDcausale = GP.causalimagazzino.IDcausale 
		    INNER JOIN GP.Lotti ON GP.Lotti.idlotto = GP.movimenti.idlotto			
			where GP.causalimagazzino.codcausale = 'S' 
			      and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (GP.Movimenti.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
			      and ((@Impianto is null) OR (GP.Impianti.IDImpianto IN (select param from GP.fn_Parametri(@Impianto, ','))))
		          and ((@CodLotto is null) OR (GP.Lotti.CodLotto Like @CodLotto))
		) DataTable
		PIVOT
		(
		   count(quantita)
		   FOR impianto IN ([RAL1],
		                    [RAL2],
		                    [Verticale],
		                    [Sublimato],
		                    [Colorwood Grande],
							[Ferro],
							[Allineamento inventario],
							[Reso],
							[Venduto],
							[Smaltito],
							[Colorwood Piccolo],
							[Inventario])
		) PivotTable
	) as ST on P1.idpolvere = ST.idpolvere
	where 
	  ((@CodicePolvere is null) OR (P1.CodicePolvere Like @CodicePolvere))
	  and ((@Famiglia is null) OR (vw_FamiglieColoriXPolveri.Famiglia = @Famiglia))
	  and ((@Applicazione is null) OR (vw_FamiglieColoriXPolveri.Applicazione = @Applicazione))
	  and ((@ColoriAbbinati is null) OR (@ColoriAbbinati  Like cteColoriAbbinati.CodiceColore  ))
	  and ((@Fornitore is null) OR (GP.AnagFornitori.Descrizione like @Fornitore))
	  and ((@Tipo is null) OR (P1.IsCodiceCustom = @Tipo))
	group by
	P1.CodicePolvere, P1.IDPolvere, P1.Descrizione, P1.ScortaMinima, P1.Note
	,GP.AnagFornitori.Descrizione
	,TC.CaricoJ, TS.ScaricoJ
	,TC.ValoreCaricoJ, TS.ValoreScaricoJ
	,TPU.PrezzoUnitario, TPU1.AAA
	,SC.[RAL1],SC.[RAL2],SC.[Verticale],SC.[Sublimato],SC.[Colorwood Grande],SC.[Ferro],SC.[Allineamento inventario],SC.[Reso],SC.[Venduto],SC.[Smaltito],SC.[Colorwood Piccolo], SC.[Inventario]
    ,ST.[RAL1],ST.[RAL2],ST.[Verticale],ST.[Sublimato],ST.[Colorwood Grande],ST.[Ferro],ST.[Allineamento inventario],ST.[Reso],ST.[Venduto],ST.[Smaltito],ST.[Colorwood Piccolo], ST.[Inventario]
    ,SCUP.[RAL1],SCUP.[RAL2],SCUP.[Verticale],SCUP.[Sublimato],SCUP.[Colorwood Grande],SCUP.[Ferro],SCUP.[Allineamento inventario],SCUP.[Reso],SCUP.[Venduto],SCUP.[Smaltito],SCUP.[Colorwood Piccolo],SCUP.[Inventario]
	,cteFamiglia.idpolvere
	,cteColoriAbbinati.codicepolvere
	,cteApplicazioni.idPolvere
	,vw_FamiglieColoriXPolveri.Famiglia
	,vw_FamiglieColoriXPolveri.Famiglia

END
